# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html

import pymysql

class ZhihuPipeline(object):
    conn=""
    def __init__(self):
        self.conn = pymysql.connect(host="127.0.0.1",user="root",passwd="root",db="zhihu",charset='utf8')

    def process_item(self, item, spider):
        try:
            #update huati_table(id,name,tid,class)
            huati_name = item["huati_name"]
            huati_tid = item["huati_tid"]
            huati_class = item["huati_class"]
            sql = "insert into zhihu.huati(name,tid,class) values('" + huati_name + "','" + huati_tid + "','" + huati_class + "')"
            self.conn.query(sql)
        except Exception as err:
            print(err)

        types = item["types"]
        if(types == "article"):
            #update articlelink(id,title,link,tid)
            try:
                article_title = item["title"]
                article_link = item["link"]
                article_tid = item["huati_tid"]
                sql = "insert into zhihu.articlelink(title,link,tid) values('" + article_title + "','" + article_link + "','" + article_tid + "')"
                self.conn.query(sql)
            except Exception as err:
                print(err)

            #update article(id,title,author,aid,content)
            try:
                article_title = item["title"]
                article_author = item["author"]
                article_aid = item["huati_tid"]
                article_content = item["content"]
                sql = "insert into zhihu.article(title,author,aid,content) values('" + article_title + "','" + article_author + "','" + article_aid + "','" + article_content +"')"
                self.conn.query(sql)
            except Exception as err:
                print(err)
        else:
            # update ask(id,title,detail,aid,link)
            try:
                ask_title = item["title"]
                ask_detail = item["question"]
                article_aid = item["huati_tid"]
                article_link = item["link"]
                sql = "insert into zhihu.ask(title,detail,aid,link) values('" + ask_title + "','" + ask_detail + "','" + article_aid + "','" + article_link + "')"
                self.conn.query(sql)
            except Exception as err:
                print(err)

            #update answer(author,content,aid)
            try:
                article_author = item["author"]
                article_aid = item["huati_tid"]
                article_content = item["content"]
                sql = "insert into zhihu.answer(author,content,aid) values('" + article_author + "','" + article_content + "','" + article_aid +"')"
                self.conn.query(sql)
            except Exception as err:
                print(err)

        return item

    def close_spider(self):
        self.conn.close()
